﻿using System;
using System.Data;
using System.Data.SqlClient;
namespace LRC
{
    class Program
    {
        public static string connectionString;
        static void Main()
        {
            try
            {
                // connectionString = "server=.;database=DB_WMSFIRST;uid=sa;pwd=1qaz;";
                connectionString = "server=172.17.96.12;database=DB_WMSFIRST;uid=spcuser;pwd=sed2016@4;";

                Console.WriteLine("");
                Console.WriteLine("正在连接服务........");
                Console.WriteLine("");
                Console.WriteLine("");
                if (ConnectionSQL())
                {
                    Console.WriteLine("服务连接成功！");
                    Console.WriteLine("");
                    Console.WriteLine("");
                    #region 
                    //  1 20181024001 PT-JX200HBU %DE8550003
                    //  ExecuteSql(" UPDATE DB_WMSFIRST.[dbo].[T_Stock] SET BK11='1' WHERE Product_Model LIKE 'PT-JX200HBU%' and  Serial_Number LIKE'%DE8550003%'  AND PutList_NO ='20181024001' ");


                    // 2 2019-03-28 处理27号PDA以其他方式出库后，又重新入库时不能入库的情况{53条数据}
                    //int result = 0;
                    #region tblDatas
                    //DataTable tblDatas = new DataTable("Datas");
                    //tblDatas.Columns.Add("Product_Model", Type.GetType("System.String"));
                    //tblDatas.Columns.Add("Serial_Number", Type.GetType("System.String"));

                    //tblDatas.Rows.Add(new object[] { "PT-MW630U", "%DD8660004" });
                    //tblDatas.Rows.Add(new object[] { "PT-MW630U", "%DD8660001" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660038" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660026" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660074" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660029" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660044" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660041" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660082" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660083" });

                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660028" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660077" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660042" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660065" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570BA", "%DE8660066" });
                    //tblDatas.Rows.Add(new object[] { "PT-VX610EJ", "%DC9150132" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9150001" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC8640003" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC8640005" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9150006" });

                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9150002" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110014" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110013" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110023" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110028" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110010" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110006" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110033" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110077" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110009" });

                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110026" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110031" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110024" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110025" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110027" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NU", "%DC9110040" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9150312" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9150355" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9150347" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9150288" });

                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9150302" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9210129" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9210131" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9210142" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9210146" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW540U", "%DC9210139" });
                    //tblDatas.Rows.Add(new object[] { "PT-RZ570WA", "%DE8650004" });
                    //tblDatas.Rows.Add(new object[] { "PT-VZ580T", "%DC9210064" });
                    //tblDatas.Rows.Add(new object[] { "PT-VZ580T", "%DC9210065" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NE", "%DC9150005" });

                    //tblDatas.Rows.Add(new object[] { "PT-VW545NE", "%DC9150007" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NE", "%DC9150008" });
                    //tblDatas.Rows.Add(new object[] { "PT-VW545NE", "%DC9150006" });
                    #endregion
                    //for (int i = 0; i < tblDatas.Rows.Count; i++)
                    //{
                    //    ExecuteSql(" UPDATE [DB_WMSFIRST].[dbo].[T_Stock]        SET  BK11='1'  WHERE                 Product_Model = '" + tblDatas.Rows[i]["Product_Model"].ToString().Trim() + "' and  Serial_Number ='" + tblDatas.Rows[i]["Serial_Number"].ToString().Trim() + "'  ");
                    //    ExecuteSql(" UPDATE [DB_WMSFIRST].[dbo].[T_StockPrepare] SET  BK11='1'  WHERE Replace(Product_Model,' ','') = '" + tblDatas.Rows[i]["Product_Model"].ToString().Trim() + "' and  Serial_Number ='" + tblDatas.Rows[i]["Serial_Number"].ToString().Trim() + "'  ");
                    //    result++;
                    //}

                    //// 3  2019-04-13 
                    //int counts =  ExecuteSql(" update T_StockPrepare set BK11 =1 where NID in ( select  a.NID from T_StockPrepare a LEFT JOIN T_Stock b on a.Product_Model = b.Product_Model and  a.Serial_Number = b.Serial_Number where b.BK07 IN ( '返工', '改造', '借机', '其他' )  AND b.Update_UserID <> b.bk01 AND b.bk11 IS NULL AND b.PickList_NO IS NOT NULL ) ");

                    // 4  2019-05-30 
                    // int counts = ExecuteSql("   DELETE FROM DB_WMSFIRST.dbo.T_Pandian WHERE BK01='20190530' AND PainDian_machine='ET-JPF200WK' AND PainDian_KW='OPT0001' ");

                    // 5  2021-04-29 
                    //int counts = ExecuteSql("UPDATE DB_WMSFIRST.dbo.T_Stock SET OutList_NO= '20210412085058' ,PickList_NO= '1Z276V9A0476336141' ,BanHao= '1',Create_Time= '2021-4-12 8:58:49',BK06='1',BK07='UPS',BK08='20210412'  WHERE NID='790937'  ");

                    //// 6  2022-06-14 
                    //ExecuteSql("UPDATE DB_WMSFIRST.dbo.T_Stock SET " +
                    //           "BanHao= '1'," +
                    //           "BK11='1'," +
                    //           "BK06='1'," +
                    //           "BK08='20220217'  " +
                    //           "WHERE Product_Model='1XY1RZ990BU7' AND Serial_Number='%DA2150011' ");

                    //ExecuteSql("UPDATE DB_WMSFIRST.dbo.T_PickStockPrepare SET " +
                    //           "OutList_NO= 'V20220607OU' ," +
                    //           "BanHao= '1'," + 
                    //           "BK06='1'," + 
                    //           "WHERE Product_Model='1XY1RZ990BU7' AND Serial_Number='%DA2150011' AND BK08='20220217'  ");
                    #endregion

                    #region MyRegion
                    //// 7  2022-06-21 
                    //ExecuteSql("UPDATE DB_WMSFIRST.dbo.T_Stock SET OutList_NO= 'V20220607OU',BanHao = '4' WHERE Product_Model='1XY1RZ990BU7' AND Serial_Number='%DA2150011' ");
                    //ExecuteSql("DELETE FROM DB_WMSFIRST.dbo.T_Stock WHERE Product_Model='ET-YFB200' AND Serial_Number='ET-YFB200' ");
                    #endregion

                    //// 8  2023-03-20 
                    //ExecuteSql("DELETE FROM [dbo].[TAutoFiles] WHERE Vison='V4.1.0.3.2.0'");


                    //// 9  2023-03-21 
                    ExecuteSql("ALTER TABLE[T_DeoSale] ADD OrderNO varchar(50) NULL");
                    
                    Console.WriteLine(" 操作完成");
                    Console.WriteLine("");
                    Console.WriteLine("");
                    Console.WriteLine("按任意键退出！");
                    Console.ReadKey();
                }
                else
                {
                    Console.WriteLine(" 操作失败！ ");
                    Console.WriteLine("");
                    Console.WriteLine("");
                    Console.WriteLine(" 按任意键退出！");
                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                Console.WriteLine(" 操作失败！ ");
                Console.WriteLine("");
                Console.WriteLine("");
                Console.WriteLine(" 按任意键退出！");
                Console.ReadKey();
            }
        }

        public static bool ConnectionSQL()
        {
            bool result = true;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(Database.ConnString);
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    result = true;
                    conn.Close();
                }
                else
                {
                    result = false;
                }
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
            }
            return result;
        }
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }
    }

}
